Re: [sqlite] Do I need to migrate to MySQL?

Mr. Puneet Kishor wrote: > On Jun 3, 2011, at 1:19 PM, Darren Duncan wrote: >> MySQL should be avoided like the plague. > > why? > > This is a long standing (un)conventional wisdom to which I too have hewed. > Now, it so happens, I will be starting work on a project that uses MySQL >

Re: [sqlite] Trapping errors in the TCL interface to sqlite (libtclsqlite3.so)

On Fri, Jun 3, 2011 at 3:01 PM, Simon Slavin wrote: > However, there are ways to do many things the dot commands do.  For instance, > > http://www.sqlite.org/pragma.html#pragma_database_list Got it, Simon, this works: set dblist [db1 eval {pragma database_list;}] puts

Re: [sqlite] Trapping errors in the TCL interface to sqlite (libtclsqlite3.so)

On 3 Jun 2011, at 9:58pm, Keith Christian wrote: > I have another question about the TCL interface: What is the syntax > to execute the commands available at the sqlite> prompt? > > Suppose I want to run the ".databases" command, usually executed from > the sqlite> prompt. There is none. The

Re: [sqlite] Trapping errors in the TCL interface to sqlite (libtclsqlite3.so)

On Fri, Jun 3, 2011 at 2:42 PM, Simon Slavin wrote: > Use the form > > CREATE TABLE IF NOT EXISTS ... > > http://www.sqlite.org/lang_createtable.html Thank you, Simon. I have another question about the TCL interface: What is the syntax to execute the commands available

Re: [sqlite] .import error: cannot open large file

On Fri, Jun 3, 2011 at 18:28, Rense Corten wrote: > Thanks for your answer, Nuno. > However, the system I am using is already 64-bit (I should have > mentioned that) , and the same binary can do the job on another Ubuntu > 64-bit  system. I'll try your suggestion nevertheless,

Re: [sqlite] Trapping errors in the TCL interface to sqlite (libtclsqlite3.so)

On 3 Jun 2011, at 9:26pm, Keith Christian wrote: > Problem - After the first pass, it obviously attempts to CREATE TABLE > again, even though the table already exists (Lines 22-25 below.) > > Is there a function provided by libtclslqite3.so that could either > list the existing tables, or trap

[sqlite] Trapping errors in the TCL interface to sqlite (libtclsqlite3.so)

I've successfully compiled libtclslqite3.so on CentOS 5.5 Linux and have run the example sqlite3 code from http://www.sqlite.org/tclsqlite.html. The TCL script below is almost verbatim from the above web page's examples. Problem - After the first pass, it obviously attempts to CREATE TABLE

Re: [sqlite] Do I need to migrate to MySQL?

On Fri, Jun 3, 2011 at 2:22 PM, Simon Slavin wrote: > > On 3 Jun 2011, at 7:19pm, Darren Duncan wrote: > >> MySQL should be avoided like the plague. > > Why ? > Coincidentally, I happened to be reading over this page just earlier today:

Re: [sqlite] Do I need to migrate to MySQL?

We use MySQL on a fairly high end PC server running Linux. The database is about 300-400 gigs. Front end of PHP and apache. It's been reliable for a couple years now and seems to have a bunch of headroom. I wrote a wrapper so my SQLite code (with minor modification) can feed MySQL instead of

Re: [sqlite] Can't download the gzip archives from the list info page

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 06/03/2011 10:36 AM, Keith Christian wrote: > What is the procedure to obtain the list archives? I'd recommend you use Gmane which has messages going back to 2003. They provide multiple different ways of viewing the messages as well as searching.

Re: [sqlite] Do I need to migrate to MySQL?

On Jun 3, 2011, at 1:19 PM, Darren Duncan wrote: > MySQL should be avoided like the plague. why? This is a long standing (un)conventional wisdom to which I too have hewed. Now, it so happens, I will be starting work on a project that uses MySQL exclusively, and has done so for years. They

Re: [sqlite] Do I need to migrate to MySQL?

On 3 Jun 2011, at 7:19pm, Darren Duncan wrote: > MySQL should be avoided like the plague. Why ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Do I need to migrate to MySQL?

MySQL should be avoided like the plague. Use Postgres instead if you have to switch to a larger SQL DBMS. But hopefully the help you've gotten so far will extend your mileage with SQLite and you won't have to switch to anything yet. -- Darren Duncan Ian Hardingham wrote: > Guys, the server

[sqlite] Can't download the gzip archives from the list info page

Hello Sqlite List, Clicking a Gzip'd Text xxxKB link on the archives page (http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users) displays a page with this message: No such list 2011-june.txt.gz What is the procedure to obtain the list archives? Thanks. ==Keith

Re: [sqlite] .import error: cannot open large file

Thanks for your answer, Nuno. However, the system I am using is already 64-bit (I should have mentioned that) , and the same binary can do the job on another Ubuntu 64-bit system. I'll try your suggestion nevertheless, but can there be other causes? Rense > It's what the thread says. The SQLite

Re: [sqlite] Do I need to migrate to MySQL?

Thanks Nico, and everyone else. A 3x speed bump is still good, so I've set journal_mode to WAL. The docs seem to suggest I only need to do this once, but I presume that doing it once every time I open the database is acceptable? Thanks, Ian On 03/06/2011 15:42, Nico Williams wrote: > > On

Re: [sqlite] Do I need to migrate to MySQL?

On 3 Jun 2011, at 3:31pm, Ian Hardingham wrote: > Should really every single INSERT/UPDATE section have a begin/end > transaction around it? That would be the slowest way to do it. It would make each section an independent transaction, and at the end of the transaction all your changes must

Re: [sqlite] Do I need to migrate to MySQL?

On Jun 3, 2011 10:31 AM, "Ian Hardingham" wrote: > > Hey guys, once again thanks for the help. > > Should really every single INSERT/UPDATE section have a begin/end > transaction around it? There's an implied begin/commit if you don't put them there. It's when you can batch

Re: [sqlite] Do I need to migrate to MySQL?

Indexes slow down writes somewhat, true, but it sounds like the OP's issue is with commit latency, the average minimum bound for which is given by the storage hardware's capabilities. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Do I need to migrate to MySQL?

On Jun 3, 2011 10:04 AM, "Ian Hardingham" wrote: > > Thank you Igor, I'll do some more thorough profiling. > > When I run the query: > > UPDATE multiturnTable SET complete=1 WHERE id=-5 > > This takes ~45ms (as reported by SQLite's profile) - is this in the > right ballpark?

Re: [sqlite] Do I need to migrate to MySQL?

I should add you only need it around MULTIPLE statements too. I don't believe there's any benefit around a single statement. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org

Re: [sqlite] Do I need to migrate to MySQL?

You only need it around INSERT or UPDATE, not SELECT. The transaction delays the commit to disk so only transactions that write benefit from it. It makes a HUGE difference in speed. You may also find increasing your cache size could help. How big is your database? Michael D. Black

[sqlite] HELP: sqlite queries execute faster in sqlite manager addon but take a lot of time to execute using a java program

This is not any update or insert statement. This is just a select statement. Will that help if i change the driver ? I am currently using sqlitejdbc-v056.jar Thanks Sridhar On 03-06-2011 18:59, Sridhar Polavarapu wrote: > Hi > > One of my sqlite query executes faster in sqlite manager( firefox

Re: [sqlite] Do I need to migrate to MySQL?

Hey guys, once again thanks for the help. Should really every single INSERT/UPDATE section have a begin/end transaction around it? I have posted this code before, so apologies for doing it again - here is how my scripting language calls a query: int SQLiteObject::ExecuteSQL(const char* sql,

Re: [sqlite] Do I need to migrate to MySQL?

And do you wrap all your updates inside a transaction? Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Ian Hardingham

Re: [sqlite] Do I need to migrate to MySQL?

I think it's very slow. Update of non-indexed fiels may be faster. Do you create a new db connection for each update?.. Or may be you have a lot of unused indicies? 2011/6/3 Ian Hardingham : > Thank you Igor, I'll do some more thorough profiling. > > When I run the query: > >

Re: [sqlite] Do I need to migrate to MySQL?

My radar says having a TEXT field as a primary key is bad (your userTable). String compares are horrendously slow. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org

Re: [sqlite] Do I need to migrate to MySQL?

Thank you Igor, I'll do some more thorough profiling. When I run the query: UPDATE multiturnTable SET complete=1 WHERE id=-5 This takes ~45ms (as reported by SQLite's profile) - is this in the right ballpark? I'm running a fairly fast modern intel chip here. Thanks, Ian

Re: [sqlite] Do I need to migrate to MySQL?

Ian Hardingham wrote: > Hey guys, thank you very much for the help so far. > > The list of calls which I make during the "end match section", which can > take 2 seconds, is: The queries you show, on the amount of data you claim, can't possibly take 2 seconds. They should run

Re: [sqlite] Do I need to migrate to MySQL?

Hey guys, thank you very much for the help so far. The list of calls which I make during the "end match section", which can take 2 seconds, is: SELECT * FROM multiturnTable WHERE id=? LIMIT 1 UPDATE multiturnTable SET p1SubmitScore=1 WHERE id=? UPDATE multiturnTable SET complete=1, score=?

[sqlite] sqlite queries execute faster in sqlite manager addon but take a lot of time to execute using a java program

Hi One of my sqlite query executes faster in sqlite manager( firefox addon ) but take a lot of time to execute using a java program. Here is the snippet PreparedStatement mStatement; ResultSet mResults; CachedRowSetImpl impl=null; Connection mconn = null;

Re: [sqlite] Testing for a null string

Try "... WHERE f IS NULL". SQL is a programming language with three-valued logic, meaning it has truth, falsehood, and null. NULL != NULL, strangely enough. Nico -- On Jun 3, 2011 8:54 AM, "Paul Sanderson" wrote: > I am sure tihs is basic but. > > I have a

Re: [sqlite] Do I need to migrate to MySQL?

On 03/06/2011, at 9:47 PM, Ian Hardingham wrote: > What is basically happening is that we're getting a fairly large number > of requests every second. There is one specific activity which takes > about 2 seconds to resolve, which is finishing a match. This requires > an update to three

[sqlite] Testing for a null string

I am sure tihs is basic but. I have a database with a text column and i want to return all rows where the column has no value I have tried select * from db where f = NULL select * from db where f = "" select * from db where f = '' all return 0 records when I knopw that most fields are empty

Re: [sqlite] Do I need to migrate to MySQL?

On Fri, Jun 3, 2011 at 6:58 AM, Ian Hardingham wrote: > > I may be looking at a complete re-write. I may also need to have a > solution which scales beyond one machine. Can anyone give me advice on > this matter specifically? > > See http://www.sqlite.org/whentouse.html for

Re: [sqlite] Non-hot journal removal (remove db locks)

On 2 Jun 2011, at 12:45pm, Jo Blogs wrote: > Is there some way to check, using code, if a journal is hot? I'd like to be > able to do this so I can automatically remove non-hot journals. > Is there an SQLite api for opening/reading journals etc.? I've looked at the > source and all the journal

Re: [sqlite] Do I need to migrate to MySQL?

On 3 Jun 2011, at 12:47pm, Ian Hardingham wrote: > Common database operations: > > - select all of my non-completed matches > - select all of my matches > - select an old match to watch > - get my current friends If any of these operations are slow, make sure you have indexes suited to your

Re: [sqlite] Do I need to migrate to MySQL?

On Fri, Jun 3, 2011 at 7:47 AM, Ian Hardingham wrote: > Thanks Eduardo, I will go into more detail. > > The core of the server is the match list. It is a table with currently > about 200,000 rows in it. > > Two players will start a match, and a new entry is placed in the >

Re: [sqlite] Do I need to migrate to MySQL?

Thanks Eduardo, I will go into more detail. The core of the server is the match list. It is a table with currently about 200,000 rows in it. Two players will start a match, and a new entry is placed in the matchTable. A typical match will last 8 turns - as each player finishes a turn, the

Re: [sqlite] Do I need to migrate to MySQL?

At 12:58 03/06/2011, you wrote: >Guys, the server for this game - > >http://www.frozensynapse.com > >uses SQLite. We've had an unexpectedly successful launch which has >resulted in the server being swamped with players, and I'm trying to >optimise everywhere I can. I've always been under the

Re: [sqlite] Do I need to migrate to MySQL?

I start tclsqlite in 16 threads on 2-core Intel Xeon servers and these work fine. 2011/6/3 Ian Hardingham : > Guys, the server for this game - > > http://www.frozensynapse.com > > uses SQLite.  We've had an unexpectedly successful launch which has > resulted in the server being

[sqlite] Do I need to migrate to MySQL?

Guys, the server for this game - http://www.frozensynapse.com uses SQLite. We've had an unexpectedly successful launch which has resulted in the server being swamped with players, and I'm trying to optimise everywhere I can. I've always been under the impression that SQLite is pefectly

[sqlite] Non-hot journal removal (remove db locks)

Hey Guys, Is there some way to check, using code, if a journal is hot? I'd like to be able to do this so I can automatically remove non-hot journals. Is there an SQLite api for opening/reading journals etc.? I've looked at the source and all the journal related stuff seems to be private. I'm in a

Re: [sqlite] Error (LOCKFILE_EXCLUSIVE_LOCK) at building sqlite project for pocket pc

Hi, Thanks a lot for your answer. What you are poiting out here is indeed very important... We are new to these "Personal Digital Assistant"/"Pocket PC" programming "problematics". We are not targeting so called "general public" devices. Our customers range from small companies with little

Re: [sqlite] Making data and tables persistent

Darren Duncan wrote: > >> I am using sqlite3 primarily from c++, everything is working fine, > >> except when I switch off my computer I loose all data, is there any > >> setting I need to do to make the data and table object persistent in the .db file? > > > > Are you correctly closing your

Re: [sqlite] HELP : how to use datetime('column', 'localtime') as a part of sql string

Here is the code of my TestStatusDate public static void main(String[] args) throws Exception { String jobQuery = "SELECT j.jobId, c.channelName , datetime(j.jobCreateDate, 'localtime'), j.jobStatus, j.jobQuality, j.jobCompleteDate FROM Job j, Channel c where c.channelId =