[sqlite] sqlite performance issues on Linux running on MIPS (BIG ENDIAN)
Hi, I am planning to use sqlite in an application program. The application will be running in Linux environment on MIPS processor (BIG ENDIAN). Are there any sqlite performance issues when running on MIPS processor with Linux as the OS. In particular are there any issues regarding non-aligned memory access (e.g. BUS errors)? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Locking in sqlite
So if A, B and C are different files then they will not block despite leaving inside the same connection, right? BTW, by "block" I mean retrying after SQLITE_BUSY too. That's very good and convenient thing. Thank you, Pavel On Sat, May 2, 2009 at 10:05 PM, Nuno Lucas wrote: > On Sat, May 2, 2009 at 3:48 PM, Pavel Ivanov wrote: > [...] >> update main.t set x = 1 >> update B.t set x = 1 >> update C.t set x = 1 >> >> My question is will these statements be able to execute in connections >> in parallel simultaneously because they use different databases and >> should lock only those databases? Or they will lock each other and >> will exhibit effectively serial execution because main database in all >> connections is the same? > > The lock is per database file, so they will only block (or fail with > SQLITE_BUSY) if done at the same time on the same database file. > > Regards, > ~Nuno Lucas > >> >> Pavel > ___ > 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] Locking in sqlite
On Sat, May 2, 2009 at 3:48 PM, Pavel Ivanov wrote: [...] > update main.t set x = 1 > update B.t set x = 1 > update C.t set x = 1 > > My question is will these statements be able to execute in connections > in parallel simultaneously because they use different databases and > should lock only those databases? Or they will lock each other and > will exhibit effectively serial execution because main database in all > connections is the same? The lock is per database file, so they will only block (or fail with SQLITE_BUSY) if done at the same time on the same database file. Regards, ~Nuno Lucas > > Pavel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Locking in sqlite
Hi, all! I wasn't able to find clear answer to my question about locking in sqlite in documentation, so please help me who knows things well. Let's say we have 3 different connections, all created with the same pattern: connect to database A, attach database B, attach database C. Then in these 3 connections we execute these 3 statements: update main.t set x = 1 update B.t set x = 1 update C.t set x = 1 My question is will these statements be able to execute in connections in parallel simultaneously because they use different databases and should lock only those databases? Or they will lock each other and will exhibit effectively serial execution because main database in all connections is the same? Pavel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Names of Indexes (CREATE INDEX)
wrote in message news:49fbde2e.1050...@gmail.com > It was frustrating and confusing becuase I have not encountered this > index-name feature in any other DB I've worked with What feature? You are not saying that no other DB you worked with allowed you to create indexes, are you? What precisely, in your opinion, is so different about how SQLite handles indexes? I don't understand your problem at all. > and its > undocumented on sqlite.org. it doesnt give any restrictions or > recommendations for index names What recommendations did you expect? You can name an index anything you want. An index name is only ever used in CREATE INDEX and DROP INDEX statements, so the exact name is largely irrelevant. > it doesnt even say when you must use > them or not use them Nothing forces you to use inexes. You may want to create some to improve performance of certain queries, but you don't have to. > so one could simply guess Guess at what? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL sintaxe
On 2/05/2009 9:13 PM, Ernany wrote: > Hello John, > > I have 2 tables ("pcprod" and "pcdesc"). I´d like to *add *in my application > "MARCA" and "DESCR". > > This code is VB2005 .NET. How do I write SQL??? You already have columns named "descr" and "marca" in the "pcprod" table. You need to add them only to the "pcdesc" table. To do this, you need to use the ALTER TABLE statement: ALTER TABLE pcdesc ADD COLUMN descr NVARCHAR(40); ALTER TABLE pcdesc ADD COLUMN marca NVARCHAR(20); Do please read all of this: http://www.sqlite.org/lang_altertable.html especially the last sentence ("After ADD COLUMN has been run on a database, that database will not be readable by SQLite version 3.1.3 and earlier."). What version of SQLite are you using? After you have defined the new columns, your SELECT statement should no longer give you an error message ... but you still need to populate those columns. I do hope this helps this time :-) Cheers, John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL sintaxe
On Sat, May 2, 2009 at 6:13 AM, Ernany wrote: > Hello John, > > I have 2 tables ("pcprod" and "pcdesc"). I´d like to *add *in my application > "MARCA" and "DESCR". > > This code is VB2005 .NET. How do I write SQL??? http://www.google.com/search?q=sql+tutorial > > Thanks a lot > > Ernany > > > '*** > > Dim dr As Data.SQLite.SQLiteDataReader > Dim BdMain As New ClsBD > Dim ContLinhas As Integer = 0 > Dim indice As Integer = 1 > Dim indiceLinha As Integer = 119 > > > BdMain.AbreBD() > > dr = BdMain.SQLRetornaDR("SELECT SUM(qtde) AS cont, codigo FROM pcdesc > GROUP BY codigo") > While dr.Read > ContLinhas = ContLinhas + 1 > SerialPortLeitura.WriteLine("T 2 0 7 " & indiceLinha & " " & > indice & "- ") 'Indice > SerialPortLeitura.WriteLine("T 2 0 96 " & indiceLinha & " " & > dr("codigo")) 'Codigo > SerialPortLeitura.WriteLine("T 2 0 403 " & indiceLinha & " " & > dr("cont")) 'Quantidade > > *add **"MARCA" and "DESCR"* *??* > > dr.Close() > > > > '***'*** > '***'*** > '***'*** > > > 2009/5/2 John Machin > >> On 2/05/2009 4:21 PM, Ernany wrote: >> > Hello, >> > >> > CREATE TABLE [pcprod] ( >> > [codigo] NVARCHAR(20) NOT NULL ON CONFLICT ABORT PRIMARY KEY, >> > [descr] NVARCHAR(40), >> > [marca] NVARCHAR(20), >> > [codigoint] NVARCHAR(20), >> > [preco] NUMERIC(5, 7), >> > [preco2] NUMERIC(5, 7)); >> > >> > >> > >> > CREATE TABLE [pcdesc] ( >> > [codigo] NVARCHAR(20), >> > [qtde] NUMERIC(5, 6), >> > [loja] INTEGER, >> > [secao] INTEGER, >> > [coletor] INTEGER, >> > [hora] NVARCHAR(8), >> > [dia] NVARCHAR(8), >> > [chave] GUID PRIMARY KEY); >> > >> > >> > This "Select " is ok. >> > SELECT SUM(qtde) AS cont, codigo FROM pcdesc GROUP BY codigo" >> > >> > >> > But I need the fields "*marca*" and "*descr*". >> > >> > select SUM(qtde) AS cont, descr , codigo FROM pcdesc GROUP BY codigo, >> > descr ? Wrong. >> >> There is no column named "descr" in the "pcdesc" table. What error code >> or message did you get from your (unspecified) interface? What did you >> not understand about the error code or message? >> >> The command-line executable "sqlite3" tells you rather explicitly: >> >> sqlite> CREATE TABLE [pcdesc] ( >> ...> [codigo] NVARCHAR(20), >> ...> [qtde] NUMERIC(5, 6), >> ...> [loja] INTEGER, >> ...> [secao] INTEGER, >> ...> [coletor] INTEGER, >> ...> [hora] NVARCHAR(8), >> ...> [dia] NVARCHAR(8), >> ...> [chave] GUID PRIMARY KEY); >> sqlite> SELECT SUM(qtde) AS cont, codigo FROM pcdesc GROUP BY codigo; >> sqlite> select SUM(qtde) AS cont, descr , codigo FROM pcdesc GROUP BY >> codigo, >> ...> descr; >> SQL error: no such column: descr >> sqlite> >> >> By the way, those brackets around the column names are quite unnecessary >> (your first SQL statement works OK withouit them, doesn't it?) and are >> NOT standard SQL. >> >> HTH, >> John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL sintaxe
Hello John, I have 2 tables ("pcprod" and "pcdesc"). I´d like to *add *in my application "MARCA" and "DESCR". This code is VB2005 .NET. How do I write SQL??? Thanks a lot Ernany '*** Dim dr As Data.SQLite.SQLiteDataReader Dim BdMain As New ClsBD Dim ContLinhas As Integer = 0 Dim indice As Integer = 1 Dim indiceLinha As Integer = 119 BdMain.AbreBD() dr = BdMain.SQLRetornaDR("SELECT SUM(qtde) AS cont, codigo FROM pcdesc GROUP BY codigo") While dr.Read ContLinhas = ContLinhas + 1 SerialPortLeitura.WriteLine("T 2 0 7 " & indiceLinha & " " & indice & "- ") 'Indice SerialPortLeitura.WriteLine("T 2 0 96 " & indiceLinha & " " & dr("codigo")) 'Codigo SerialPortLeitura.WriteLine("T 2 0 403 " & indiceLinha & " " & dr("cont")) 'Quantidade *add **"MARCA" and "DESCR"* *??* dr.Close() '***'*** '***'*** '***'*** 2009/5/2 John Machin > On 2/05/2009 4:21 PM, Ernany wrote: > > Hello, > > > > CREATE TABLE [pcprod] ( > > [codigo] NVARCHAR(20) NOT NULL ON CONFLICT ABORT PRIMARY KEY, > > [descr] NVARCHAR(40), > > [marca] NVARCHAR(20), > > [codigoint] NVARCHAR(20), > > [preco] NUMERIC(5, 7), > > [preco2] NUMERIC(5, 7)); > > > > > > > > CREATE TABLE [pcdesc] ( > > [codigo] NVARCHAR(20), > > [qtde] NUMERIC(5, 6), > > [loja] INTEGER, > > [secao] INTEGER, > > [coletor] INTEGER, > > [hora] NVARCHAR(8), > > [dia] NVARCHAR(8), > > [chave] GUID PRIMARY KEY); > > > > > > This "Select " is ok. > > SELECT SUM(qtde) AS cont, codigo FROM pcdesc GROUP BY codigo" > > > > > > But I need the fields "*marca*" and "*descr*". > > > > select SUM(qtde) AS cont, descr , codigo FROM pcdesc GROUP BY codigo, > > descr? Wrong. > > There is no column named "descr" in the "pcdesc" table. What error code > or message did you get from your (unspecified) interface? What did you > not understand about the error code or message? > > The command-line executable "sqlite3" tells you rather explicitly: > > sqlite> CREATE TABLE [pcdesc] ( > ...> [codigo] NVARCHAR(20), >...> [qtde] NUMERIC(5, 6), >...> [loja] INTEGER, >...> [secao] INTEGER, >...> [coletor] INTEGER, >...> [hora] NVARCHAR(8), >...> [dia] NVARCHAR(8), >...> [chave] GUID PRIMARY KEY); > sqlite> SELECT SUM(qtde) AS cont, codigo FROM pcdesc GROUP BY codigo; > sqlite> select SUM(qtde) AS cont, descr , codigo FROM pcdesc GROUP BY > codigo, >...> descr; > SQL error: no such column: descr > sqlite> > > By the way, those brackets around the column names are quite unnecessary > (your first SQL statement works OK withouit them, doesn't it?) and are > NOT standard SQL. > > HTH, > John > ___ > 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
[sqlite] Sqlite on Arm EP9302 processor
I am looking at using Sqlite on my TS-7250 board. (Debian 2.4) I currently cross-compile target software on a Win PC using Cygwin & gcc-3.3.4-glibc-2.3.2 xross Toolchain. Has anyone any comments on suitability or Installation ? thanks -Alan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL sintaxe
On 2/05/2009 4:21 PM, Ernany wrote: > Hello, > > CREATE TABLE [pcprod] ( > [codigo] NVARCHAR(20) NOT NULL ON CONFLICT ABORT PRIMARY KEY, > [descr] NVARCHAR(40), > [marca] NVARCHAR(20), > [codigoint] NVARCHAR(20), > [preco] NUMERIC(5, 7), > [preco2] NUMERIC(5, 7)); > > > > CREATE TABLE [pcdesc] ( > [codigo] NVARCHAR(20), > [qtde] NUMERIC(5, 6), > [loja] INTEGER, > [secao] INTEGER, > [coletor] INTEGER, > [hora] NVARCHAR(8), > [dia] NVARCHAR(8), > [chave] GUID PRIMARY KEY); > > > This "Select " is ok. > SELECT SUM(qtde) AS cont, codigo FROM pcdesc GROUP BY codigo" > > > But I need the fields "*marca*" and "*descr*". > > select SUM(qtde) AS cont, descr , codigo FROM pcdesc GROUP BY codigo, > descr? Wrong. There is no column named "descr" in the "pcdesc" table. What error code or message did you get from your (unspecified) interface? What did you not understand about the error code or message? The command-line executable "sqlite3" tells you rather explicitly: sqlite> CREATE TABLE [pcdesc] ( ...> [codigo] NVARCHAR(20), ...> [qtde] NUMERIC(5, 6), ...> [loja] INTEGER, ...> [secao] INTEGER, ...> [coletor] INTEGER, ...> [hora] NVARCHAR(8), ...> [dia] NVARCHAR(8), ...> [chave] GUID PRIMARY KEY); sqlite> SELECT SUM(qtde) AS cont, codigo FROM pcdesc GROUP BY codigo; sqlite> select SUM(qtde) AS cont, descr , codigo FROM pcdesc GROUP BY codigo, ...> descr; SQL error: no such column: descr sqlite> By the way, those brackets around the column names are quite unnecessary (your first SQL statement works OK withouit them, doesn't it?) and are NOT standard SQL. HTH, John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Names of Indexes (CREATE INDEX)
Thank you. Alexander A. Gnatyna wrote: > you can name indexes as you wish. if you use select - you should specify > coluns names. engine well decide what index to use depend on CREATE INDEX > statement and you SQL query. > > On Saturday 02 May 2009 02:45:03 a a wrote: >> Do index names need to be different from field names? >> When doing selects on indexed fields, can I use the field name, or must I >> use the index name? >> >> I just want normal indexes, i'm not looking to create indexes for multiple >> fields. >> >> Can I say: CREATE INDEX x ON t (x); ? >> or does it have to be: CREATE INDEX index_t_x ON t (x); ? >> assuming the latter, can I say SELECT * FROM t ORDER BY x; or do I have to >> say ORDER BY index_t_x; to receive the speed benefit of the index. >> >> I tried CREATE INDEX x ON t (x); in SQLite3.exe, and no error was reported, >> but I don't want to assume anything. >> >> This is extremely ambiguous and confusing. >> I've got enough field names, I don't want to have to start dealing with >> unique index names as well in SELECT/INSERT/UPDATE queries. >> >> Thanks for the help. Feeling very frustrated atm. >> ___ >> 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 > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Names of Indexes (CREATE INDEX)
Thanks Igor!!! It was frustrating and confusing becuase I have not encountered this index-name feature in any other DB I've worked with, and its undocumented on sqlite.org. it doesnt give any restrictions or recommendations for index names, it doesnt even say when you must use them or not use them, so one could simply guess, or would have to ask on the support forum, as I have. Igor Tandetnik wrote: > a a wrote: >> Do index names need to be different from field names? > > No. > >> When doing selects on indexed fields, can I use the field name, or >> must I use the index name? > > You must use the field name, you cannot use index name. An index will be > used automatically if the engine deems it beneficial. > >> Can I say: CREATE INDEX x ON t (x); ? > > Yes. > >> or does it have to be: CREATE INDEX index_t_x ON t (x); ? > > This is valid, too. > >> assuming the latter, can I say SELECT * FROM t ORDER BY x; > > Yes. > >> or do I >> have to say ORDER BY index_t_x; > > No. In fact, it won't work, you'll get a syntax error. > >> This is extremely ambiguous and confusing. > > What specifically seems to be the problem? > >> I've got enough field names, I don't want to have to start dealing >> with unique index names as well in SELECT/INSERT/UPDATE queries. > > Wherever did you get the idea in the first place? > > 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