headers
James Scott | 4 Jul 21:48

How to refer to a multiple-column primary key (PK) as 1 column (field) name

I have the following:

CREATE TABLE [Sections] (
  [Department] varchar NOT NULL COLLATE NOCASE,
  [Course] varchar NOT NULL COLLATE NOCASE,
  [Section] varchar NOT NULL COLLATE NOCASE,
  [Class_Time] timestamp,
  [I_Id] varchar COLLATE NOCASE,
  [Room] varchar COLLATE NOCASE,
  CONSTRAINT [sqlite_autoindex_Sections_1] PRIMARY KEY ([Department],
[Course], [Section]));

CREATE INDEX [PK_Sections] ON [Sections] ([Department], [Course],
[Section]);

In the programming language, I need to refer to the primary key as 1 field.
Does Sqlite allow a 'calculated field', such as concatenation of the 3
columns in the PK?  Or can I create a View to combine the 3?

Any help is greatly appreciated.

Thanks, Bradley
_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Permalink | Reply | 1 comment |
headers
Alexey Pechnikov | 4 Jul 13:03

SQLite counters by "key" and "tempkey" extensions

Hello!

There is simple example for "tempkey" extension (Public Domain license):

create table colors(name text);
insert into colors values ('Red');
insert into colors values ('Green');
insert into colors values ('Blue');

select tempkey_install(); -- temp table "tempkeys" wiil be created
select tempkey_increment('','',1) as counter, name from colors;
1|Red
2|Green
3|Blue

select tempkey_get('',''); -- current values for key name '' in key group name ''
3

--select tempkey_delete('',''); -- delete key name '' in key group name ''
select tempkey_uninstall(); -- delete all keys

The "key" extension is similar but is operate with non-temp table "keys".

Test script for module "key":
select 'ERROR' where key_install() is not null; --Create "keys" table.
select 'ERROR' where key_exists('common','nds%')!=0; --Check is param exists
select 'ERROR' where key_get('common','nds%') is not null; --Get _non-existing_ value _without_ default_value
select 'ERROR' where key_get('common','nds%',18)!='18'; --Get _non-existing_ value _with_ default_value
select 'ERROR' where key_set('common','nds%',10)!='10'; --Set value
select 'ERROR' where key_get('common','nds%')!='10'; --Get _existing_ value
(Continue reading)

Permalink | Reply | 0 comments |
headers
freshie2004 | 4 Jul 11:46

sqlite-undo: loadable extension to give undo/redo functionality

Hi All,

As part of a project I am toying with writing I
needed undo/redo functionality, so have ended up writing a loadable
extension for sqlite which implements undo/redo functionality entirely
within the database using custom functions. Kind-of a C implementation
of http://www.sqlite.org/cvstrac/wiki?p=UndoRedo.

Only tested on Linux, so far.

Anyhoo... have fun if you are interested.

http://sourceforge.net/projects/sqlite-undo/

Cheers!

      ____________________________________________________________________________________
Access Yahoo!7 Mail on your mobile. Anytime. Anywhere.
Show me how: http://au.mobile.yahoo.com/mail
_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Permalink | Reply | 0 comments |
headers
Wenton Thomas | 4 Jul 11:29
Favicon

problem with SQLITE_BUSY

Now in my system I used sqlite  to manage  2  database file A.db and B.db,  and each has a connection handle cA, cB.
My operation perform like this:

sqlite3_exec( select records from cA)
sqlite3_exec("begin transaction");
insert all records  into cB;
sqlite3_exec("commit transaction");

All  return value is normal.,but when  I  execute     
rc = sqlite3_close(), 
return value rc always be SQLITE_BUSY.

Could anyone help me?

Does the  two database connection disturb each other?
I means, if  there exist a  reading lock on cA, can I write cB?

      
_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Permalink | Reply | 5 comments |
headers
Rick Ratchford | 4 Jul 04:05

Limit Rows Retrieved

Perhaps to solve the larger problem I have in a different post, I have a
question on retrieving records.

How do you request a fixed number of records starting from a location in the
data based searched for, all in one SQL statement?

"SELECT Date = '2009-01-01' ...plus the next x number of records"

If I had such a statement, I believe it would go a long way to solve my
bigger problem.

Thank you in advance.
Rick

_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Permalink | Reply | 1 comment |
headers
Rick Ratchford | 3 Jul 23:02

SQL Query Question

Can someone help me with this?

Suppose you have a Table/Recordset that has these columns:

Date  (string)
Color (string)
Offset (long)

I want to extract from this Table/Recordset 40 contiguous records from 15
locations within the dataset, each referenced by the Date.

For example, say I have 15 Dates already extracted by a previous query.

I need to now get the 40 records that start at each of those 15 Dates.

================

Suppose my 15 Dates are:

2009-03-03
2008-11-05
2008-07-10
...
...
2007-07-23

 
Assuming this is a SORTED dataset in ascending order by Date, I would need
to extract 40 records that start with the record at 2009-03-03, then 40
records starting with the record at 2008-11-05, and so-forth.
(Continue reading)

Permalink | Reply | 5 comments |
headers
Marcus Grimm | 3 Jul 19:33
Favicon

How can a COMMIT attempt result in SQLITE_BUSY ?

Hello List,

I'm wondering how it can happen that after a successfull
"BEGIN TRANSACTION;" statement and some inserts or update
statements, that the final COMMIT failes with a busy error code ?

Shouldn't that be impossible because there can be only one running
transaction ?

I'm using shared cache and read_uncommitted = True inside a
server application that opens multible connection to the same
DB (similar to what can be found on the wiki pages under SampleCode).

Everthing was working perfect until the user started a maintenance
tool that opens a single connection to the same sqlite database
while the server application was still running on the same hardware.
Now it happends that the server was not able to get a
COMMIT statement through (i.e. he timeout while trying this
via sqlite3_step); with the result that he left a transaction
open in one thread and was no longer able to do any writes
into the sqlite database.
After rebooting the server of course everthing was working back again.

I'm able to reproduce this with a modified version of the
SampleCode by just starting it two times: usually a COMMIT will
timeout after a while.

I know some workarounds for this (mainly to really make sure that
a COMMIT can't be timed out anymore) but I'm curious if there is
an explanation for this.
(Continue reading)

Permalink | Reply | 2 comments |
headers
James Gregurich | 3 Jul 04:25

referential integrity and INSERT OR REPLACE


question:

How do I maintain referential integrity on a INSERT OR REPLACE given  
it does not call the delete trigger on the offending rows?

thanks,
james
_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Permalink | Reply | 7 comments |
headers
knightfeng | 3 Jul 09:02
Favicon

How to do 5,000,000 "select"s as fast as possible

Dear all,
      We have to do 5,000,000 "select"s from a database with 40000 record (using C API). We do it as follow:
1. "create table refGene (geneName vchar, geneID vchar, chromo vchar, strand char(1), txStart number,
txEnd number, cdsStart number, cdsEnd number, exonNum number, exonStart vchar, exonEnd vchar)"

2.  insert ....  40000 records.

3.  rc = sqlite3_exec(db, "create index indexwig on refGene (chromo, txStart, txEnd)" , NULL , NULL, &zErrMsg);

4. repeat 5,000,000  {
       sprintf(sqlCmd, "select geneName,exonStart,exonEnd from refGene where chromo=='%s' and txStart <= %d
and     txEnd>=%d", one.chromo.c_str(), one.start, one.end);

       rc = sqlite3_prepare(db, sqlCmd, strlen(sqlCmd), &stmt, NULL);
       rc = sqlite3_step(stmt);
       while(rc == SQLITE_ROW)
      {  
            ......
            rc = sqlite3_step(stmt);
       }
        rc = sqlite3_finalize(stmt);
}

The 5,000,000 "select"s take about 30 minutes in our machine (3Gb memory, 2 x 1.8G Hz CPU). 

Are there some faster ways to use sqlite to do the 5,000,000 "select"s ?

Thanks

Zhixing
(Continue reading)

Permalink | Reply | 2 comments |
headers
chandan | 3 Jul 08:28

Value returned by sqlite3_column_bytes for strings

Hi all,
    Consider the following scenario,
    1. A table contains a column of type "text".
    2. The value of this column for the first row is say "linux".

If we execute the SQL statement: "select name from some_tbl where id = ?"
using sqlite3_step() API, then what is the value returned by 
sqlite3_column_bytes(). Does the count include the '\0' byte (count == 
6). If it does not include the '\0' byte the count should be 5.

I executed the following program:

/******************************************************************************/
#include <stdio.h>
#include <stdint.h>
#include <stdlib.h>
#include <string.h>
#include "sqlite3.h"

const char *update_db = "update some_tbl set name = ? where id = ?";
const char *read_db = "select name from some_tbl where id = ?";

int32_t main(int argc, char *argv[])
{
    sqlite3_stmt *stmt;
    sqlite3 *db;
    int32_t num_bytes;
    char buf[100];
    int32_t ret;
(Continue reading)

Permalink | Reply | 2 comments |
headers
Kermit Mei | 3 Jul 05:12

How to import an empty value(NULL) into database from a file?

Hello, how can I import an empty value into the database from a file.
The filed may be int or string, if I write "NULL" , then I'll read a
string "NULL" from it. I hope that I can get an empty value (Eg, an
empty string whose size() is zero in Qt).

How can I do ?
Thanks.

Kermit

_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Permalink | Reply | 1 comment |

Gmane